package org.restorator.dao.implement;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.restorator.dao.base.UserDao;
import org.restorator.dao.implement.engin.ItemRowMapper;
import org.restorator.dao.implement.engin.OrderEnricher;
import org.restorator.dao.implement.engin.OrderRowMapper;
import org.restorator.dao.implement.engin.UserEnricher;
import org.restorator.dao.implement.engin.UserRowMapper;
import org.restorator.dao.jdbc.AbstractJdbsTemplate;
import org.restorator.dao.jdbc.Enricher;
import org.restorator.dao.jdbc.PreparedSetter;
import org.restorator.dao.jdbc.SimpleResultSetExtractor;
import org.restorator.dao.tx.TransactionManager;
import org.restorator.entity.User;
import org.restorator.exception.DataStoreException;
import org.restorator.utill.GeneralUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class UserDaoImpl extends AbstractJdbsTemplate<User, Integer> implements UserDao {

    public UserDaoImpl() { }

    private static Logger logger = LoggerFactory.getLogger(GeneralUtil.getClassName());


    private static final String SQL_SELECT_ALL = "SELECT USER_ID, USER_NAME, USER_SURNAME, USER_PHONE, USER_LOGIN, USER_PASSWORD, USER_KEY, USER_LLT, ROLE_ID FROM USERS";
    private static final String SQL_SELECT_BY_ID = "SELECT USER_ID, USER_NAME, USER_SURNAME, USER_PHONE, USER_LOGIN, USER_PASSWORD, USER_KEY, USER_LLT, ROLE_ID FROM USERS WHERE USER_ID = ?";
    private static final String SQL_SELECT_BY_ORDER_ID = "SELECT u.USER_ID, u.USER_NAME, u.USER_SURNAME, u.USER_PHONE, u.USER_LOGIN, u.USER_PASSWORD, u.USER_KEY, u.USER_LLT, u.ROLE_ID FROM USERS u, ORDERS o WHERE o.ORDER_ID = ?";
    private static final String SQL_UPDATE_USER = "UPDATE USERS SET USER_NAME = ?, USER_SURNAME = ?, USER_PHONE = ?, USER_LOGIN = ?, USER_PASSWORD = ?, ROLE_ID = ?, USER_KEY = ? WHERE USER_ID = ?";
    private static final String SQL_DELETE_USER = "DELETE FROM USERS WHERE USER_ID = ?";
    
    private static final String SQL_SAVE_USER = "INSERT INTO USERS VALUES(?, ?, ?, ?, ?, ?, ?)";
    private static final String SQL_GET_USER_BY_LOGIN_AND_PASS = "SELECT USER_ID, USER_NAME, USER_SURNAME, USER_PHONE,  USER_LOGIN, USER_PASSWORD, USER_KEY, USER_LLT, ROLE_ID  FROM USERS WHERE USER_LOGIN = ? AND USER_PASSWORD = ?";
    private static final String SQL_GET_USER_BY_KEY = "SELECT USER_ID, USER_NAME, USER_SURNAME, USER_PHONE, USER_PHONE, USER_LOGIN, USER_PASSWORD, USER_KEY, USER_LLT, ROLE_ID FROM USERS WHERE USER_KEY = ?";
    
    @SuppressWarnings("unchecked")
	@Override
    public User get(final Integer integer) throws DataStoreException {
    	return executeParameter(SQL_SELECT_BY_ID,
                new SimpleResultSetExtractor(new UserRowMapper()),
                Enricher.NULL, new PreparedSetter() {
            
            public void setParameters(PreparedStatement ps) throws SQLException {
                ps.setInt(1, integer);
            }
        });
    }

   
	@SuppressWarnings("unchecked")
	@Override
    public User update(final User user) throws DataStoreException {
        logger.trace("update user: {}", user);
    	if(user != null && user.getUserId() != 0) {
            updateOne(SQL_UPDATE_USER, user, Enricher.NULL, new PreparedSetter() {
                @Override
                public void setParameters(PreparedStatement ps) throws SQLException {
                    ps.setString(1, user.getName());
                    ps.setString(2, user.getSurname());
                    ps.setString(3, user.getPhone());
                    ps.setString(4, user.getLogin());
                    ps.setString(5, user.getPassword());
                    ps.setInt(6, user.getRoleId());
                    ps.setString(7, user.getKey());
                    ps.setInt(8, user.getUserId());
                }
            });
            return user;
        } else {
	        return null;  
	    }
    }

    @SuppressWarnings("unchecked")
	@Override
    public void delete(final User deleted) throws DataStoreException {
    	executeParameter(SQL_DELETE_USER,
                new SimpleResultSetExtractor(new UserRowMapper()),
                Enricher.NULL,
                new PreparedSetter() {
                    @Override
                    public void setParameters(PreparedStatement ps) throws SQLException {
                        ps.setInt(1, deleted.getUserId());
                    }
                });
    }

    @Override
	public long count() {
		// TODO Auto-generated method stub
		return 0;
	}

    @SuppressWarnings("unchecked")
	@Override
    public Integer save(final User us, Object... addParams) throws DataStoreException {
    	executeInsert(SQL_SAVE_USER, us, Enricher.NULL,
                new PreparedSetter() {
                    @Override
                    public void setParameters(PreparedStatement ps) throws SQLException {
                        ps.setInt(1, us.getUserId());
                        ps.setString(2, us.getName());
                        ps.setString(3, us.getSurname());
                        ps.setString(4, us.getLogin());
                        ps.setString(5, us.getPassword());
                        ps.setString(6, us.getPhone());
                        ps.setInt(7, us.getRoleId());
                    }
                });
        return us.getUserId();  
    }

    @Override
    public List<User> getAll() throws DataStoreException {
    	return list(
    			SQL_SELECT_ALL,
        new SimpleResultSetExtractor(new UserRowMapper()),
        Enricher.NULL);
    }

    @Override
    public boolean exist(Integer integer) {
        return false;  //To change body of implemented methods use File | Settings | File Templates.
    }


    @SuppressWarnings("unchecked")
	@Override
    public User getUserByOrderId(final int orderId) throws DataStoreException {
    	return executeParameter(SQL_SELECT_BY_ORDER_ID,
                new SimpleResultSetExtractor(new UserRowMapper()), Enricher.NULL,
                new PreparedSetter() {
            
                    @Override
            public void setParameters(PreparedStatement ps) throws SQLException {
                ps.setInt(1, orderId);
            }
        }); 
    }


	@Override
	public User getUserByLoginAndPass(final String password, final String login) throws DataStoreException {
        if(password == null || login == null) {
            return null;
        }
		return executeParameter(SQL_GET_USER_BY_LOGIN_AND_PASS,
                new SimpleResultSetExtractor(new UserRowMapper()), Enricher.NULL,
                new PreparedSetter() {
            
                    @Override
            public void setParameters(PreparedStatement ps) throws SQLException {
                        ps.setString(2, password);
                        ps.setString(1, login);

            }
        }); 
	}

    @Override
    public User getUserByKey(final String key) throws DataStoreException {
        return executeParameter(SQL_GET_USER_BY_KEY, 
                new SimpleResultSetExtractor(new UserRowMapper()), Enricher.NULL,
                new PreparedSetter() {

            @Override
            public void setParameters(PreparedStatement ps) throws SQLException {
                ps.setString(1, key);
            }
        });
    }
}
